USE [LocalDB]
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Common.ExceptionLevel')) BEGIN
	CREATE TABLE Common.ExceptionLevel
	(
		ID int NOT NULL IDENTITY(1,1),
		Name varchar(20) NOT NULL,
		[Level] int NOT NULL,

		CONSTRAINT PK_Common_ExceptionLevel PRIMARY KEY (ID ASC)
	)

	INSERT INTO Common.ExceptionLevel (Name, [Level])
	VALUES ('Exception', 1), ('Warning', 11)
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Common.Exception')) BEGIN
	CREATE TABLE Common.Exception
	(
		ID uniqueidentifier NOT NULL,
		[Message] varchar(MAX) NOT NULL,
		[Source] varchar(MAX) NOT NULL,
		StackTrace varchar(MAX) NOT NULL,
		ParentID uniqueidentifier NOT NULL,

		CONSTRAINT PK_Common_Exception PRIMARY KEY (ID ASC)
	)
END
GO

IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Gene.Source')) BEGIN
	CREATE TABLE Gene.[Source]
	(
		ID int NOT NULL IDENTITY(1,1),
		Name varchar(20) NOT NULL,
		[Key] varchar(20) NOT NULL,

		CONSTRAINT PK_Gene_Source PRIMARY KEY (ID ASC)
	)

	CREATE UNIQUE INDEX IX_Gene_Source_Key ON Gene.[Source] ([Key] ASC)

	INSERT INTO Gene.[Source] (Name, [Key])
	VALUES ('FASTA', 'FASTA'), ('BLASTN (NCBI)', 'BLASTN_NCBI'), ('GenBank Search', 'GenBank'), ('Ensembl Search', 'Ensembl')
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Gene.SequenceType')) BEGIN
	CREATE TABLE Gene.SequenceType
	(
		ID int NOT NULL IDENTITY(0,1),
		Name varchar(20) NOT NULL,
		[Key] varchar(20) NOT NULL,

		CONSTRAINT PK_Gene_SequenceType PRIMARY KEY (ID ASC)
	)

	CREATE UNIQUE INDEX IX_Gene_SequenceType_Key ON Gene.SequenceType ([Key] ASC)

	INSERT INTO Gene.SequenceType (Name, [Key])
	VALUES ('Not Defined', 'NotDefined'), ('Source', 'Source'), ('Gene', 'Gene'), ('Coding Sequence', 'Coding'), ('Alignment', 'Alignment')
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Gene.Gene')) BEGIN
	CREATE TABLE Gene.Gene
	(
		ID uniqueidentifier NOT NULL,
		[Definition] varchar(1000) NOT NULL,
		SourceID int NOT NULL,

		/* GenBank specific fields
			We're putting these here because presumably most of our records will come from GenBank.  Some of the fields could be filled out from 
			searching Ensembl and local BlastN databases.
		*/
		GenBankID int NULL,
		Locus varchar(100) NULL,
		Accession varchar(20) NULL,
		Organism varchar(250) NULL,
		Taxonomy varchar(4000) NULL, -- Yup, I'm going to need to parse this.

		Nucleotides varchar(MAX) NULL,
		SequenceTypeID int NULL,

		SequenceStart int NULL,
		SequenceEnd int NULL,
		CodingSequenceStart int NULL,
		CodingSequenceEnd int NULL,

		LastUpdatedAt datetime2(7) NULL,
		Active bit NOT NULL CONSTRAINT DF_Gene_Gene_Active DEFAULT (1),

		CONSTRAINT PK_Gene_Gene PRIMARY KEY (ID ASC)
	)

	ALTER TABLE Gene.Gene ADD CONSTRAINT FK_Gene_Gene_SourceID FOREIGN KEY (SourceID) REFERENCES Gene.[Source] (ID)
	ALTER TABLE Gene.Gene ADD CONSTRAINT FK_Gene_Gene_SequenceTypeID FOREIGN KEY (SequenceTypeID) REFERENCES Gene.SequenceType (ID)
	CREATE UNIQUE INDEX IX_Gene_Gene_GenBankID ON Gene.Gene (GenBankID ASC)
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Gene.GeneHistory')) BEGIN
	CREATE TABLE Gene.GeneHistory
	(
		RevisionID int NOT NULL,
		ID uniqueidentifier NOT NULL,
		SourceID int NOT NULL,
		GenBankID int NULL,
		Locus varchar(100) NULL,
		[Definition] varchar(1000) NULL,
		Accession varchar(20) NULL,
		Organism varchar(250) NULL,
		Taxonomy varchar(4000) NULL,
		Nucleotides varchar(MAX) NULL,
		SequenceTypeID int NULL,
		SequenceStart int NULL,
		SequenceEnd int NULL,
		CodingSequenceStart int NULL,
		CodingSequenceEnd int NULL,
		LastUpdatedAt datetime2(7) NOT NULL,
		Active bit NOT NULL

		CONSTRAINT PK_Gene_GeneHistory PRIMARY KEY (RevisionID ASC, ID ASC)
	)
	
	ALTER TABLE Gene.GeneHistory ADD CONSTRAINT FK_Gene_GeneHistory_ID FOREIGN KEY (ID) REFERENCES Gene.Gene (ID)
	ALTER TABLE Gene.GeneHistory ADD CONSTRAINT FK_Gene_GeneHistory_SourceID FOREIGN KEY (SourceID) REFERENCES Gene.[Source] (ID)
	ALTER TABLE Gene.GeneHistory ADD CONSTRAINT FK_Gene_GeneHistory_SequenceTypeID FOREIGN KEY (SequenceTypeID) REFERENCES Gene.SequenceType (ID)
END
GO
--IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Gene.BlastN')) BEGIN
--	-- Additional data about a Gene, as retrieved from a BlastN output file.
--	CREATE TABLE Gene.BlastN
--	(
--		ID uniqueidentifier NOT NULL, -- Should be 1-1 with a row in Gene.Gene
--		ContigNumber varchar(100) NULL,
--		SourceFile varchar(250) NULL,

--		CONSTRAINT PK_Gene_BlastN PRIMARY KEY (ID ASC)
--	)
--END
--GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Gene.ExonOrientation')) BEGIN
	CREATE TABLE Gene.ExonOrientation
	(
		ID int NOT NULL IDENTITY(1,1),
		Name varchar(10) NOT NULL,
		[Key] varchar(10) NOT NULL,

		CONSTRAINT PK_Gene_ExonOrientation PRIMARY KEY (ID ASC)
	)

	INSERT INTO Gene.ExonOrientation (Name, [Key])
	VALUES ('Plus/Plus', 'PlusPlus'), ('Plus/Minus', 'PlusMinus')
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Gene.Exception')) BEGIN
	CREATE TABLE Gene.Exception
	(
		GeneID uniqueidentifier NOT NULL,
		ExceptionID uniqueidentifier NOT NULL,
		ExceptionLevel int NOT NULL,

		CONSTRAINT PK_Gene_Exception PRIMARY KEY (GeneID ASC, ExceptionID ASC)
	)

	ALTER TABLE Gene.Exception ADD CONSTRAINT FK_Gene_Exception_GeneID FOREIGN KEY (GeneID) REFERENCES Gene.Gene (ID)
	ALTER TABLE Gene.Exception ADD CONSTRAINT FK_Gene_Exception_ExceptionID FOREIGN KEY (ExceptionID) REFERENCES Common.Exception (ID)
END
GO

IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('BlastN.Alignment')) BEGIN
	CREATE TABLE BlastN.Alignment
	(
		ID uniqueidentifier NOT NULL,
		QueryID uniqueidentifier NOT NULL,
		SubjectID uniqueidentifier NOT NULL,
		[Rank] int NOT NULL,
		
		CONSTRAINT PK_BlastN_Alignment PRIMARY KEY (ID ASC)
	)

	ALTER TABLE BlastN.Alignment ADD CONSTRAINT FK_BlastN_Alignment_QueryID FOREIGN KEY (QueryID) REFERENCES Gene.Gene (ID)
	ALTER TABLE BlastN.Alignment ADD CONSTRAINT FK_BlastN_Alignment_SubjectID FOREIGN KEY (SubjectID) REFERENCES Gene.Gene (ID)
	
	CREATE UNIQUE INDEX IX_BlastN_Alignment ON BlastN.Alignment (QueryID ASC, SubjectID ASC)
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('BlastN.AlignmentExon')) BEGIN
	CREATE TABLE BlastN.AlignmentExon
	(
		ID uniqueidentifier NOT NULL,
		AlignmentID uniqueidentifier NOT NULL,
		OrientationID int NOT NULL,
		BitScore float NOT NULL,
		AlignmentLength int NOT NULL,
		IdentitiesCount int NOT NULL,
		Gaps int NOT NULL,
		QueryRangeStart int NOT NULL,
		QueryRangeEnd int NOT NULL,
		SubjectRangeStart int NOT NULL,
		SubjectRangeEnd int NOT NULL,
		
		CONSTRAINT PK_BlastN_AlignmentExon PRIMARY KEY (ID ASC)
	)

	ALTER TABLE BlastN.AlignmentExon ADD CONSTRAINT FK_BlastN_AlignmentExon_AlignmentID FOREIGN KEY (AlignmentID) REFERENCES BlastN.Alignment (ID)
	ALTER TABLE BlastN.AlignmentExon ADD CONSTRAINT FK_BlastN_AlignmentExon_OrientationID FOREIGN KEY (OrientationID) REFERENCES Gene.ExonOrientation (ID)
END
GO

IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('RecordSet.RecordSet')) BEGIN
	CREATE TABLE RecordSet.RecordSet
	(
		ID uniqueidentifier NOT NULL,
		Name varchar(200) NOT NULL,
		CreatedAt datetime2(7) CONSTRAINT DF_RecordSet_RecordSet_CreatedAt DEFAULT (sysdatetime()),
		LastOpenedAt datetime2(7) NULL,
		ModifiedAt datetime2(7) CONSTRAINT DF_RecordSet_RecordSet_ModifiedAt DEFAULT (sysdatetime()),
		Active bit NOT NULL CONSTRAINT DF_RecordSet_RecordSet_Active DEFAULT(1),

		CONSTRAINT PK_RecordSet_RecordSet PRIMARY KEY (ID ASC)
	)
	CREATE UNIQUE INDEX IX_RecordSet_RecordSet_Name ON RecordSet.RecordSet (Name ASC, Active ASC)
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('RecordSet.GeneStatus')) BEGIN
	CREATE TABLE RecordSet.GeneStatus
	(
		ID int NOT NULL IDENTITY(1,1),
		Name varchar(20) NOT NULL,
		[Key] varchar(20) NOT NULL,

		CONSTRAINT PK_RecordSet_GeneStatus PRIMARY KEY (ID ASC)
	)

	CREATE UNIQUE INDEX IX_RecordSet_GeneStatus_Key ON RecordSet.GeneStatus ([Key] ASC)

	INSERT INTO RecordSet.GeneStatus (Name, [Key])
	VALUES ('Committed', 'Committed'), ('Pending', 'Pending')
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('RecordSet.Gene')) BEGIN
	CREATE TABLE RecordSet.Gene
	(
		RecordSetID uniqueidentifier NOT NULL,
		GeneID uniqueidentifier NOT NULL,
		GeneStatusID int NOT NULL,
		ModifiedAt datetime2(7) NOT NULL CONSTRAINT DF_RecordSet_Gene_ModifiedAt DEFAULT (sysdatetime()),

		CONSTRAINT PK_RecordSet_Gene PRIMARY KEY (RecordSetID ASC, GeneID ASC, GeneStatusID ASC)
	)

	ALTER TABLE RecordSet.Gene ADD CONSTRAINT FK_RecordSet_Gene_RecordSetID FOREIGN KEY (RecordSetID) REFERENCES RecordSet.RecordSet (ID)
	ALTER TABLE RecordSet.Gene ADD CONSTRAINT FK_RecordSet_Gene_GeneID FOREIGN KEY (GeneID) REFERENCES Gene.Gene (ID)
	ALTER TABLE RecordSet.Gene ADD CONSTRAINT FK_RecordSet_Gene_GeneStatusID FOREIGN KEY (GeneStatusID) REFERENCES RecordSet.GeneStatus (ID)
END
GO

IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Job.[Target]')) BEGIN
	CREATE TABLE Job.[Target]
	(
		ID int NOT NULL IDENTITY(1,1),
		Name varchar(20) NOT NULL,
		[Key] varchar(20) NOT NULL,

		CONSTRAINT PK_Job_Target PRIMARY KEY (ID ASC)
	)

	INSERT INTO Job.[Target] (Name, [Key])
	VALUES ('BLASTN (NCBI)', 'BLASTN_NCBI'), ('BLASTN (Local)', 'BLASTN_Local'), ('Ensembl', 'Ensembl')
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Job.[Status]')) BEGIN
	CREATE TABLE Job.[Status]
	(
		ID int NOT NULL IDENTITY(1,1),
		Name varchar(20) NOT NULL,
		[Key] varchar(20) NOT NULL,

		CONSTRAINT PK_Job_Status PRIMARY KEY (ID ASC)
	)

	INSERT INTO Job.[Status] (Name, [Key])
	VALUES ('New', 'New'), ('Running', 'Running'), ('Completed', 'Completed'), ('Cancelled', 'Cancelled'), ('Reviewed', 'Reviewed'), ('Archived', 'Archived')
END
GO
--SELECT * FROM Job.[Status]
--DBCC CHECKIDENT ('Job.Status', RESEED, 3)
--DELETE FROM Job.[Status] WHERE ID > 3
--INSERT INTO Job.[Status] (Name, [Key])
--	VALUES ('Cancelled', 'Cancelled'), ('Reviewed', 'Reviewed'), ('Archived', 'Archived')
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Job.Job')) BEGIN
	CREATE TABLE Job.Job
	(
		ID uniqueidentifier NOT NULL,
		RecordSetID uniqueidentifier NULL,
		TargetID int NOT NULL,
		StartedAt datetime2(7) NULL, -- No default on this; long-term, a user might want to set up a job and then run it later, in which case it hasn't started yet.
		EndedAt datetime2(7) NULL,
		StatusID int NOT NULL CONSTRAINT DF_Job_Job_StatusID DEFAULT (1),

		CONSTRAINT PK_Job_Job PRIMARY KEY (ID ASC)
	)

	ALTER TABLE Job.Job ADD CONSTRAINT FK_Job_Job_TargetID FOREIGN KEY (TargetID) REFERENCES Job.[Target] (ID)
	ALTER TABLE Job.Job ADD CONSTRAINT FK_Job_Job_StatusID FOREIGN KEY (StatusID) REFERENCES Job.[Status] (ID)
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Job.GeneDirection')) BEGIN
	CREATE TABLE Job.GeneDirection
	(
		ID int NOT NULL IDENTITY(1,1),
		Name varchar(20) NOT NULL,
		[Key] varchar(20) NOT NULL,

		CONSTRAINT PK_Job_GeneDirection PRIMARY KEY (ID ASC)
	)

	INSERT INTO Job.GeneDirection (Name, [Key])
	VALUES ('Input', 'Input'), ('Output', 'Output')
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Job.Gene')) BEGIN
	CREATE TABLE Job.Gene
	(
		JobID uniqueidentifier NOT NULL,
		GeneID uniqueidentifier NOT NULL,
		DirectionID int NOT NULL,

		CONSTRAINT PK_Job_Gene PRIMARY KEY (JobID ASC, GeneID ASC, DirectionID ASC)
	)

	ALTER TABLE Job.Gene ADD CONSTRAINT FK_Job_Gene_JobID FOREIGN KEY (JobID) REFERENCES Job.Job (ID)
	ALTER TABLE Job.Gene ADD CONSTRAINT FK_Job_Gene_GeneID FOREIGN KEY (GeneID) REFERENCES Gene.Gene (ID)
	ALTER TABLE Job.Gene ADD CONSTRAINT FK_Job_Gene_DirectionID FOREIGN KEY (DirectionID) REFERENCES Job.GeneDirection (ID)
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Job.InputFileFormat')) BEGIN
	CREATE TABLE Job.InputFileFormat
	(
		ID int NOT NULL IDENTITY(1,1),
		Name varchar(20) NOT NULL,

		CONSTRAINT PK_Job_InputFileFormat PRIMARY KEY (ID ASC)
	)

	INSERT INTO Job.InputFileFormat (Name)
	VALUES ('FASTA'), ('BLASTN'), ('GenBank')
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Job.InputFile')) BEGIN
	CREATE TABLE Job.InputFile
	(
		ID uniqueidentifier NOT NULL,
		JobID uniqueidentifier NOT NULL,
		Name varchar(250) NOT NULL,
		FileFormatID int NOT NULL,
		LastModifiedAt datetime2(7) NOT NULL,

		CONSTRAINT PK_Job_InputFile PRIMARY KEY (ID ASC)
	)

	ALTER TABLE Job.InputFile ADD CONSTRAINT FK_Job_InputFile_FileFormatID FOREIGN KEY (FileFormatID) REFERENCES Job.InputFileFormat (ID)
END
GO

IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('NCBI.Request')) BEGIN
	CREATE TABLE NCBI.Request
	(
		ID uniqueidentifier NOT NULL, -- I'm not 100% certain whether NCBI recycles RequestIDs
		RequestID varchar(20) NOT NULL,
		JobID uniqueidentifier NOT NULL,
		StartTime datetime2(7) NOT NULL CONSTRAINT DF_NCBI_Request_StartTime DEFAULT (sysdatetime()),
		EndTime datetime2(7) NULL,
		LastStatus varchar(8) NULL,
		LastUpdatedAt datetime2(7) NOT NULL CONSTRAINT DF_NCBI_Request_LastUpdatedAt DEFAULT (sysdatetime()),
		StatusInformation varchar(MAX) NULL,

		CONSTRAINT PK_NCBI_Request PRIMARY KEY (ID ASC)
	)

	ALTER TABLE NCBI.Request ADD CONSTRAINT FK_NCBI_Request_JobID FOREIGN KEY (JobID) REFERENCES Job.Job (ID)
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('NCBI.Gene')) BEGIN
	CREATE TABLE NCBI.Gene
	(
		RequestID uniqueidentifier NOT NULL,
		GeneID uniqueidentifier NOT NULL,
		DirectionID int NOT NULL,

		CONSTRAINT PK_NCBI_Gene PRIMARY KEY (RequestID ASC, GeneID ASC, DirectionID ASC)
	)

	ALTER TABLE NCBI.Gene ADD CONSTRAINT FK_NCBI_Gene_RequestID FOREIGN KEY (RequestID) REFERENCES NCBI.Request (ID)
	ALTER TABLE NCBI.Gene ADD CONSTRAINT FK_NCBI_Gene_GeneID FOREIGN KEY (GeneID) REFERENCES Gene.Gene (ID)
	ALTER TABLE NCBI.Gene ADD CONSTRAINT FK_NCBI_Gene_DirectionID FOREIGN KEY (DirectionID) REFERENCES Job.GeneDirection (ID)
END
GO
IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('NCBI.BlastNAlignment')) BEGIN
	CREATE TABLE NCBI.BlastNAlignment
	(
		RequestID uniqueidentifier NOT NULL,
		AlignmentID uniqueidentifier NOT NULL,

		CONSTRAINT PK_NCBI_BlastNAlignment PRIMARY KEY (RequestID ASC, AlignmentID ASC)
	)

	ALTER TABLE NCBI.BlastNAlignment ADD CONSTRAINT FK_NCBI_BlastNAlignment_RequestID FOREIGN KEY (RequestID) REFERENCES NCBI.Request (ID)
	ALTER TABLE NCBI.BlastNAlignment ADD CONSTRAINT FK_NCBI_BlastNAlignment_AlignmentID FOREIGN KEY (AlignmentID) REFERENCES BlastN.Alignment (ID)
END
GO

IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID('Common.ApplicationProperties')) BEGIN
	CREATE TABLE Common.ApplicationProperty
	(
		ID uniqueidentifier NOT NULL,
		[Key] varchar(30) NOT NULL,
		Value varchar(MAX) NULL,

		CONSTRAINT PK_Common_ApplicationProperty PRIMARY KEY (ID ASC)
	)

	INSERT INTO Common.ApplicationProperty (ID, [Key], Value) VALUES 
	VALUES (newid(), 'FASTAFileNameFormatString', '{Definition} {Coding Sequence Range Start}-{Coding Sequence Range End}'),
			(newid(), 'FASTAHeaderFormatString', '{Definition} {Coding Sequence Range Start}-{Coding Sequence Range End}'),
			(newid(), 'DatabaseVersion', '1.0.0.0')

	CREATE UNIQUE INDEX IX_Common_ApplicationProperty_Key ON Common.ApplicationProperty ([Key] ASC)
END
GO